/*
 * Created on Feb 16, 2005
 */
package com.scalar.chiptrack.finance.openpo.dao;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Location;
import com.scalar.chiptrack.finance.openpo.dao.local.LocalPurchaseOrderDAOImpl;

import com.scalar.chiptrack.finance.openpo.forms.OpenPurchaseOrderForm;
import com.scalar.chiptrack.finance.openpo.forms.PurchaseOrderReceiptForm;
import com.scalar.chiptrack.finance.openpo.forms.PurchaseOrderSearchForm;
import com.scalar.chiptrack.finance.openpo.forms.ReceiptSearchForm;

import com.scalar.chiptrack.operations.vendor.Vendor;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.sales.dao.SalesDAO;
import com.scalar.chiptrack.finance.openpo.forms.PurchaseOrderForm;
import com.scalar.chiptrack.finance.openpo.*;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import org.apache.struts.util.LabelValueBean;

/**
 * @author Ravikanth
**/
public class PurchaseOrderDAO implements Constants
{
	private static PurchaseOrderDAO sPurchaseOrderDAO;
	private static IPurchaseOrderDAO sIPurchaseOrderDAO;

	static
	{
		String finIntegrationType = ConfigManager.getFinanceIntegrationType();

		if ( ( finIntegrationType == null ) || ( finIntegrationType.equalsIgnoreCase( FINANCE_INTEGRATION_TYPE_LOCAL ) ) )
		{
			sIPurchaseOrderDAO = new LocalPurchaseOrderDAOImpl();
		}

	}

	public synchronized static PurchaseOrderDAO getInstance()
	{
		if ( sPurchaseOrderDAO == null )
		{
			sPurchaseOrderDAO = new PurchaseOrderDAO();
		}
		return sPurchaseOrderDAO;
	}

	/**
	 * Singleton - Private constructor
	 */
	private PurchaseOrderDAO()
	{
	}

	/**
	 * Loads all the Vendors having Fab, Sort, Assembly, Test
	 *
	 * Used in Create
	 * 1. Purchase Order Screen
	 *
	 * @throws ScalarException
	 */
	public static ArrayList loadPOVendors() throws ScalarException
	{
		return sIPurchaseOrderDAO.loadPOVendors();
	}

	/**
	 * Loads all the Vendors
	 *
	 * Used in Create
	 * 1. Open Purchase Order Search Screen
	 *
	 * @throws ScalarException
	 */
	public static ArrayList loadAllVendors() throws ScalarException
	{
		return sIPurchaseOrderDAO.loadAllVendors();
	}

	/**
	 * Open PO Search
	 *
	 * Used in Create
	 * 1. Open Purchase Order Search Screen
	 *
	 * @param purchaseOrderSearchForm forma bean
	 * @throws ScalarException   exception
	 */
	public static ArrayList searchOpenPO( PurchaseOrderSearchForm purchaseOrderSearchForm ) throws ScalarException
	{
		return sIPurchaseOrderDAO.searchOpenPO( purchaseOrderSearchForm );
	}


	/**
	 * @return Pruchase Order Number
	 */
	public static String loadAutomatedPurchaseOrderNo() throws ScalarException
	{
		return sIPurchaseOrderDAO.loadAutomatedPurchaseOrderNo();
	}

	/**
	 * @return Client Address from com
	 */
	public static Address getClientAddress() throws ScalarException
	{
		return sIPurchaseOrderDAO.getClientAddress();
	}

	/*
	 *  Loads the Purchase Order Types
	 */
    public static ArrayList loadPurchaseOrderTypes()
    {
        ArrayList purchaseOrderTypesList 	= new ArrayList();
        purchaseOrderTypesList.add ( new LabelValueBean ("1", "Open PO"));
        purchaseOrderTypesList.add ( new LabelValueBean ("2", "Standard"));
        //purchaseOrderTypesList.add ( new LabelValueBean ("3", "DropShip"));

        return purchaseOrderTypesList;
    }
	/*
     *
	 *  Loads the Purchase Order Status
	 */
	public static ArrayList loadPurchaseOrderStatusTypes( boolean loadCancelledAndChangedStatusTypes )
	{
		ArrayList purchaseOrderStatusTypesList 	= new ArrayList();
		purchaseOrderStatusTypesList.add( new LabelValueBean( PURCHASE_ORDER_STATUS_NEW, PURCHASE_ORDER_STATUS_NEW ) );
		purchaseOrderStatusTypesList.add( new LabelValueBean( PURCHASE_ORDER_STATUS_RELEASED, PURCHASE_ORDER_STATUS_RELEASED ) );
		purchaseOrderStatusTypesList.add( new LabelValueBean( PURCHASE_ORDER_STATUS_RECEIVED, PURCHASE_ORDER_STATUS_RECEIVED ) );
        if( loadCancelledAndChangedStatusTypes )
        {
		    purchaseOrderStatusTypesList.add( new LabelValueBean( PURCHASE_ORDER_STATUS_CLOSED, PURCHASE_ORDER_STATUS_CLOSED ) );
		    purchaseOrderStatusTypesList.add( new LabelValueBean( PURCHASE_ORDER_STATUS_CANCELLED, PURCHASE_ORDER_STATUS_CANCELLED ) );
        }
		return purchaseOrderStatusTypesList;
	}

	/*
	 *  Loads Finance Holds
	 */
	public static ArrayList loadFinanceHolds() throws ScalarException
	{
		ArrayList finResonCodes = SalesDAO.loadFinanceReasonCodes();
		finResonCodes.add( 0, new LabelValueBean( "", "0" ) );
		finResonCodes.add( 1, new LabelValueBean( "X", "-1" ) );

		return finResonCodes;
	}

	/*
	 *  Loads Opearations Holds
	 */
	public static ArrayList loadOperationsHolds() throws ScalarException
	{
		ArrayList opsResonCodes = SalesDAO.loadOpsReasonCodes();
		opsResonCodes.add( 0, new LabelValueBean( "", "0" ) );
		opsResonCodes.add( 1, new LabelValueBean( "X", "-1" ) );

		return opsResonCodes;
	}
	/**
	 * @param poNumber checks the poNuber
	 */
	public static boolean isPurchaseOrderNumberExists( String poNumber ) throws ScalarException
	{
		return sIPurchaseOrderDAO.isPurchaseOrderNumberExists( poNumber );
	}

    public static ArrayList getYearList ()
    {
        int start_year = YEAR_START;
        int end_year = YEAR_END;

        ArrayList yearList = new ArrayList ();
        for ( int year = start_year; year <= end_year; year++ )
        {
            yearList.add ( new LabelValueBean ( year + "", year + "" ) );
        }
        return yearList;
    }


	/**
	 * @param purchaseOrderForm
	 */
	public static void saveOpenPurchaseOrder(OpenPurchaseOrderForm purchaseOrderForm, String userName ) throws ScalarException
	{
		sIPurchaseOrderDAO.saveOpenPurchaseOrder( purchaseOrderForm, userName );
	}

	/**
	 * @param purchaseOrderForm
	 */
	public static void loadOpenPurchaseOrder(OpenPurchaseOrderForm purchaseOrderForm ) throws ScalarException
	{
		sIPurchaseOrderDAO.loadOpenPurchaseOrder( purchaseOrderForm );
	}

	/**
	 * @param purchaseOrderSearchForm
	 * @return
	 */
	public static ArrayList searchPurchaseOrder(PurchaseOrderSearchForm purchaseOrderSearchForm) throws ScalarException
	{
		return sIPurchaseOrderDAO.searchPurchaseOrder( purchaseOrderSearchForm );
	}

	/**
	 * @return All The PO Products
	 */
	public static ArrayList loadPOProductsList() throws ScalarException
	{
		return sIPurchaseOrderDAO.loadPOProductsList();
	}

    public static ArrayList loadPackageTypeList () throws ScalarException
    {

        ArrayList packageTypeList 	= new ArrayList();

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        try
        {
            conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            pstmt 	= conn.prepareStatement( "select package_type from purchaseOrder_package_types_table order by package_type" );
            rs 		= pstmt.executeQuery();

            while( rs.next() )
            {
                packageTypeList.add ( new LabelValueBean ( rs.getString("package_type"), rs.getString("package_type") ) );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not Load Package Types ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in Load Package Types List" );
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in Load Package Types List" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in Load Package Types List" );
                }
            }
        }
        return packageTypeList;
    }


    public static ArrayList loadPartNumbersByVendorIdAndLocation( int vendorId, String locationType ) throws ScalarException
    {
        ArrayList partNumbersList 	= new ArrayList();
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        try
        {
            conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            pstmt 	= conn.prepareStatement( "SELECT P.ProcessStep_ID, CASE PS.PS_Type_Name WHEN 'TEST' THEN P.Part_Number+'/'+PS.PS_Type WHEN 'SORT' THEN P.Part_Number+'/'+PS.PS_Type ELSE P.Part_Number END AS Part_Number FROM Process_Step PS INNER JOIN Product P ON PS.ProcessStep_ID = P.ProcessStep_ID INNER JOIN Process_Step_Location_Table PSLT ON PS.ProcessStep_ID = PSLT.ProcessStep_ID INNER JOIN Location L ON PSLT.Location_ID = L.Location_ID WHERE PS.PS_Type_Name = ? AND L.Vendor_ID = ? Order By Part_number" );
            pstmt.setString( 1, locationType );
            pstmt.setInt   ( 2, vendorId );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                partNumbersList.add( new LabelValueBean( rs.getString( "Part_Number" ), rs.getString( "ProcessStep_ID" ) ) );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not Load Purchase Order Vendor Locations List", e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in Load Purchase Order Vendor Locations List" );
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in Load Purchase Order Vendor Locations List" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in Load Purchase Order Vendor Locations List" );
                }
            }
        }
        return partNumbersList;
    }

    public static ArrayList loadProcessNames( int vendorId ) throws ScalarException
    {
        ArrayList vendorLocationsList = new ArrayList();
        Connection conn               = null;
        PreparedStatement pstmt       = null;
        ResultSet rs                  = null;

        try
        {
            conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            pstmt 	= conn.prepareStatement( "SELECT DISTINCT Location.L_Type AS LocationType, CASE Location.L_Type WHEN 'Fab' THEN 1 WHEN 'Sort' THEN 2 WHEN 'Assembly' THEN 3 WHEN 'Test' THEN 4 END AS LOCT_FOR_SORT FROM Vendor INNER JOIN Location ON Vendor.Vendor_ID = Location.Vendor_ID WHERE Vendor.Vendor_ID = ? AND Vendor.V_Is_Deleted = 'N' AND Location.L_Is_Deleted = 'N' AND Location.L_Type IN ('FAB','SORT','Assembly','TEST') ORDER BY LOCT_FOR_SORT" );
            pstmt.setInt( 1, vendorId );
            rs 		= pstmt.executeQuery();

            while( rs.next() )
            {
                vendorLocationsList.add( new LabelValueBean( rs.getString( "LocationType" ), rs.getString( "LocationType" ) ) );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not Load Purchase Order Vendor Locations List", e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in Load Purchase Order Vendor Locations List" );
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in Load Purchase Order Vendor Locations List" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in Load Purchase Order Vendor Locations List" );
                }
            }
        }
        return vendorLocationsList;
    }

    /**
     * @param purchaseOrderForm
     */
    public static void savePurchaseOrder(PurchaseOrderForm purchaseOrderForm, String userName ) throws ScalarException
    {
        sIPurchaseOrderDAO.savePurchaseOrder( purchaseOrderForm, userName );
    }

    /**
     * method to load all purchase orders
     * @param purchaseOrderForm
     */
    public static PurchaseOrderForm loadPurchaseOrder(PurchaseOrderForm purchaseOrderForm ) throws ScalarException
    {
        return sIPurchaseOrderDAO.loadPurchaseOrder( purchaseOrderForm, false );
    }

    /**
     * method to load cancelled purchase orders
     * @param purchaseOrderForm
     * @return
     * @throws ScalarException
     */
    public static PurchaseOrderForm loadCancelledPurchaseOrder(PurchaseOrderForm purchaseOrderForm ) throws ScalarException
    {
        return sIPurchaseOrderDAO.loadPurchaseOrder( purchaseOrderForm, true );
    }

	/**
	 * @param purchaseOrder_Id loading the Po's releated to this poID
	 */
	public static ArrayList loadRelatedPos( int purchaseOrder_Id ) throws ScalarException
	{
		return sIPurchaseOrderDAO.loadRelatedPos( purchaseOrder_Id );
	}

	/**
	 * @param purchaseOrder_Id loading the Po's releated to this poID
	 * @return   arrayList
	 */
	public static ArrayList loadPOAuditTrail(int purchaseOrder_Id) throws ScalarException
	{
		return sIPurchaseOrderDAO.loadPOAuditTrail( purchaseOrder_Id );
	}

	/**
	 * @param vendor_id
	 * @return
	 */
	public static ArrayList loadVendorShippingAddresses(int vendor_id) throws ScalarException
	{
		return sIPurchaseOrderDAO.loadVendorShippingAddresses( vendor_id );
	}

	public static void setVendorNameAndShippingAddresses( PurchaseOrderForm purchaseOrderForm ) throws ScalarException
	{
		Vendor vendor 	= VendorDAO.getVendorDetails( purchaseOrderForm.getVendor_id() );
		purchaseOrderForm.setVendorName( vendor.getVendorName() );

		ArrayList addressList = PurchaseOrderDAO.loadVendorShippingAddresses( purchaseOrderForm.getVendor_id() );
		ArrayList contactPersonList = new ArrayList();
		Address address 	= null;
		LabelValueBean bean = null;

		for( int i = 0; i < addressList.size(); i++ )
		{
			address = (Address)addressList.get( i );

			if( "0".equalsIgnoreCase( address.getDefaultAddress() ) )
			{
				purchaseOrderForm.setShippingAddress( address );
				purchaseOrderForm.setShippingAddress_id( address.getAddressId() );

				if( address.getContactPersonAddressId() == -1 )
				{
					purchaseOrderForm.setContactPerson( address.getContactPerson() );
				}
				else
				{
					purchaseOrderForm.setContactPerson( "-1" );
				}
			}

			if( address.getContactPersonAddressId() == -1 )
			{
				bean = new LabelValueBean(  "-- Select --", "-1" );
			}
			else
			{
                if( "0".equalsIgnoreCase( address.getDefaultAddress() ) )
			    {
				    bean = new LabelValueBean( address.getContactPerson(), address.getContactPersonAddressId()+"" );
                    purchaseOrderForm.setContactPerson( address.getContactPersonAddressId()+"" );
                }
                else
                {
                    bean = new LabelValueBean( address.getContactPerson(), address.getContactPersonAddressId()+"" );
                }
			}
			contactPersonList.add( bean );
		}

		purchaseOrderForm.setShippingContactPersonList( contactPersonList );
		purchaseOrderForm.setShippingAddressList( addressList );

		if( purchaseOrderForm.getShippingAddress_id() == -1 )
		{
			purchaseOrderForm.setShippingAddress( ((Location)vendor.getLocationsList().get( 0 )).getLocationAddress() );
			purchaseOrderForm.setShippingAddress_id( ((Location)vendor.getLocationsList().get( 0 )).getLocationAddress().getAddressId() );
		}
	}


	/**
	 * @param receiptSearchForm
	 * @return
	 */
	public static ArrayList searchRecciptOrder(ReceiptSearchForm receiptSearchForm) throws ScalarException
	{
		return sIPurchaseOrderDAO.searchRecciptOrder( receiptSearchForm );
	}

	/**
	 * @param receiptSearchForm
	 * @return
	 */
	public static ArrayList searchPurchaseOrderForReceipts(ReceiptSearchForm receiptSearchForm) throws ScalarException
	{
		return sIPurchaseOrderDAO.searchPurchaseOrderForReceipts( receiptSearchForm );
	}

	/**
	 * @return Receipt Number
	 */
	public static String loadAutomatedReceiptNo() throws ScalarException
	{
		return sIPurchaseOrderDAO.loadAutomatedReceiptNo();
	}

	/**
	 * @param receiptNumber
	 * @return
	 */
	public static boolean isReceiptNumberExists( String receiptNumber ) throws ScalarException
	{
		return sIPurchaseOrderDAO.isReceiptNumberExists( receiptNumber );
	}

	/**
	 * @param purchaseOrderReceiptForm
	 * @param userName
	 */
	public static void savePurchaseOrderReceipt(PurchaseOrderReceiptForm purchaseOrderReceiptForm, String userName ) throws ScalarException
	{
		sIPurchaseOrderDAO.savePurchaseOrderReceipt( purchaseOrderReceiptForm, userName );
	}

	/**
	 * @param purchaseOrderReceiptForm
	 */
	public static void loadPurchaseOrderReceipt(PurchaseOrderReceiptForm purchaseOrderReceiptForm) throws ScalarException
	{
		sIPurchaseOrderDAO.loadPurchaseOrderReceipt( purchaseOrderReceiptForm );
	}

    public static Address getAddress(int addressId) throws ScalarException
    {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         Address address = null;

         try {
             conn = DataSource.getConnection();

             pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.getAddress"));
             pstmt.setInt (1, addressId);

             rs = pstmt.executeQuery();

             if (rs.next() == false) {
                 // Address not found, ???Throw exception
             }
             else {
                 address = new Address(rs.getInt("Address_ID"),
                                       rs.getString("Address_1"),
                                       rs.getString("Address_2"),
                                       rs.getString("City"),
                                       rs.getString("State"),
                                       rs.getString("ZipCode"),
                                       rs.getString("Country"),
                                       rs.getString("Phone_Number"),
                                       rs.getString("Fax_Number"),
                                       rs.getString("Email"),
                                       rs.getString("Contact_Person"),
                                       rs.getString("Cell_Number"));
                 address.setAddress3(rs.getString("Address_3"));
                 address.setAddress4(rs.getString("Address_4"));

                 //address.setContactPersons(loadContactPersons(addressId));
             }
         }
         catch (Exception sqlex) {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get Address from database", sqlex);
         }
         finally {
             if (rs != null) {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }
         return address;
     }

	/**
	 * @param vendor_Id
	 * @param processType
	 * @return
	 */
	public static ArrayList loadPartDescription( int vendor_Id, String processType )throws ScalarException
	{
		return sIPurchaseOrderDAO.loadPartDescription( vendor_Id, processType );
	}

	/*
	 * Returns the Purchase Order Number By Open Purchase Order Number.
	 */
    public static String loadAutomatedPurchaseOrderNoByOpenPurchaseOrderNo( OpenPurchaseOrderForm openPurchaseOrderForm )  throws ScalarException
    {
        Connection conn 		            = null;
        PreparedStatement pstmt             = null;
        ResultSet rs 			            = null;
        StringBuffer purchaseOrderNo 	    = new StringBuffer(20);
        int totalPos                        = 0;

        try
        {
            conn  	= DataSource.getConnection();
            pstmt 	= conn.prepareStatement( "SELECT COUNT(*) AS PO_COUNT FROM PURCHASEORDER_TABLE WHERE OPENPO_ID = ?" );
            pstmt.setInt( 1, openPurchaseOrderForm.getPurchaseOrder_id() );
            rs 		= pstmt.executeQuery();

            if( rs.next() )
            {
                totalPos = rs.getInt( "PO_COUNT" );
            }

            totalPos++;
            purchaseOrderNo.append( openPurchaseOrderForm.getPoNumber() ).append("-").append( totalPos );
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Automated Purchase Order No By Open Purchase Order No from database", sqlex );
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return purchaseOrderNo.toString();
    }

    public static ArrayList loadPOTaxesList() throws ScalarException
	{
		return sIPurchaseOrderDAO.loadPOTaxesList();
	}

    public static boolean doesPOTaxStateExist ( DBTransaction dbTransaction,String state ) throws ScalarException
	{
		return sIPurchaseOrderDAO.doesPOTaxStateExist( dbTransaction,  state );
	}

    public static void savePOTax( String states, String tax ) throws ScalarException
	{
	    sIPurchaseOrderDAO.savePOTax( states, tax );
	}

    public static double getTaxValue( String state ) throws ScalarException
	{
		return sIPurchaseOrderDAO.getTaxValue( state );
	}

    public static ArrayList loadPackTypeList () throws ScalarException
    {

        ArrayList packageTypeList 	= new ArrayList();

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        try
        {
            conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            pstmt 	= conn.prepareStatement( "select package_type from purchaseOrder_package_types_table order by package_type" );
            rs 		= pstmt.executeQuery();

            while( rs.next() )
            {
                packageTypeList.add (rs.getString("package_type") );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not Load Package Types ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in Load Package Types List" );
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in Load Package Types List" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in Load Package Types List" );
                }
            }
        }
        return packageTypeList;
    }

    public static void savePakageType( DBTransaction dbTransaction, ArrayList packageTypeList ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        boolean success = false;

        if (packageTypeList == null)
        {
            return;
        }

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ("delete from purchaseOrder_package_types_table");
            pstmt.executeUpdate();

            if(pstmt != null)
            {
                pstmt.close();
                pstmt =  null;
            }

            pstmt = conn.prepareStatement ("insert into purchaseOrder_package_types_table( Package_Type ) values( ? )");

            int size = packageTypeList.size();

            for(int i=0; i<size; i++)
            {
                String pakageType = (String)packageTypeList.get( i );
                pstmt.setString (1, pakageType);

                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }

        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Save Sort lot Bin information", sqle);
        }

        finally
        {
        // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if(rs != null)
            {
                try
                {
                    rs.close();
                }
                catch(SQLException ignore){}
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore){}
            }
        }
    }
    public static void savePODetails( DBTransaction dbTransaction, ArrayList<PurchaseOrder> poList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        PurchaseOrder purchaseOrder = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            /*pstmt = conn.prepareStatement ("UPDATE PurchaseOrder_Line_Item_Table SET STATUS = 'Closed' ");
            pstmt.executeUpdate();

            if(pstmt != null)
            {
                pstmt.close();
                pstmt =  null;
            }*/

            pstmt = conn.prepareStatement ("{ CALL SP_CREATE_UPDATE_PO_DETAILS( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

            int size = poList.size();

            for(int i=0; i<size; i++)
            {
                purchaseOrder = poList.get( i );
                pstmt.setString (1, purchaseOrder.getPurchaseOrderNumber() );
                pstmt.setString (2, purchaseOrder.getVendorName() );
                pstmt.setString (3, purchaseOrder.getPoLineItemNo() );
                pstmt.setInt (4, purchaseOrder.getPurchaseOrderType() );
                pstmt.setString (5, purchaseOrder.getPurchaseOrderDate() );
                pstmt.setString (6, purchaseOrder.getPartNumber() );
                pstmt.setInt (7, purchaseOrder.getRequestedQty() );
                pstmt.setString (8, purchaseOrder.getStatus() );
                pstmt.setString (9, purchaseOrder.getRequestedDate() );
                pstmt.setString (10, purchaseOrder.getPsType() );
                pstmt.setDouble (11, purchaseOrder.getPoUnitCost() );
                
                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }

        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Save PO information", sqle);
        }

        finally
        {
        // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if(rs != null)
            {
                try
                {
                    rs.close();
                }
                catch(SQLException ignore){}
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore){}
            }
        }
    }
    public static PurchaseOrderLineItemLotDataEntryList loadLotsForReceipts ( DBTransaction dbTransaction, String poNumber, int processStepId, int vendorId, int receiptLineItemId, int receiptId ) throws ScalarException
    {
        return sIPurchaseOrderDAO.loadLotsForReceipts( dbTransaction,  poNumber, processStepId, vendorId, receiptLineItemId, receiptId );
    }

    public static int getPOMaxLineItemNo ( DBTransaction dbTransaction, int poId ) throws ScalarException
    {
        return sIPurchaseOrderDAO.getPOMaxLineItemNo( dbTransaction,  poId );
    }

    public static void loadLineItemPrice ( DBTransaction dbTransaction, PurchaseOrderLineItemList lineItemList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        int size = ( lineItemList != null ) ? lineItemList.size() : 0;
        if ( size > 0 )
        {
            try
            {
                if ( dbTransaction != null )
                {
                    conn = dbTransaction.getConnection();
                }
                else
                {
                    conn = DataSource.getConnection();
                }
                pstmt = conn.prepareStatement ( "SELECT ( CASE WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 1 THEN ISNULL( Q1_Standard_Cost, 0.0 ) WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 2 THEN ISNULL( Q2_Standard_Cost, 0.0 ) WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 3 THEN ISNULL( Q3_Standard_Cost, 0.0 ) WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 4 THEN ISNULL( Q4_Standard_Cost, 0.0 ) END ) AS STANDARD_COST FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = ? AND DATEPART( year , FIN_YEAR ) = DATEPART( year , CURRENT_TIMESTAMP )" );
                for ( int i=0; i < lineItemList.size(); i++ )
                {
                    PurchaseOrderLineItem purchaseOrderLineItem = (PurchaseOrderLineItem) lineItemList.get ( i );
                    if ( purchaseOrderLineItem.getUnitCost() == 0 && purchaseOrderLineItem.getProcessStepId() > 0 )
                    {
                        pstmt.setInt( 1,  purchaseOrderLineItem.getProcessStepId() ); //STANDARD_COST
                        rset = pstmt.executeQuery();
                        if ( rset.next() )
                        {
                            purchaseOrderLineItem.setUnitCost( rset.getDouble( "STANDARD_COST" ) );
                        }
                        pstmt.clearParameters();
                        rset.close();
                        rset = null;
                    }
                }
            }
            catch ( Exception e )
            {
                throw new ScalarException ( "Could not load PO Line Items Unit Costs", e );
            }
            finally
            {
                if ( rset != null )
                {
                    try { rset.close(); } catch ( SQLException ignore ) {}
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch ( SQLException ignore ) {}
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch ( SQLException ignore ) {}
                }
            }
        }
    }
    public static void loadLineItemPrice ( DBTransaction dbTransaction, ReceivingLineItemList lineItemList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        int size = ( lineItemList != null ) ? lineItemList.size() : 0;
        if ( size > 0 )
        {
            try
            {
                if ( dbTransaction != null )
                {
                    conn = dbTransaction.getConnection();
                }
                else
                {
                    conn = DataSource.getConnection();
                }
                pstmt = conn.prepareStatement ( "SELECT ( CASE WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 1 THEN ISNULL( Q1_Standard_Cost, 0.0 ) WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 2 THEN ISNULL( Q2_Standard_Cost, 0.0 ) WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 3 THEN ISNULL( Q3_Standard_Cost, 0.0 ) WHEN DATEPART ( quarter , CURRENT_TIMESTAMP ) = 4 THEN ISNULL( Q4_Standard_Cost, 0.0 ) END ) AS STANDARD_COST FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = ? AND DATEPART( year , FIN_YEAR ) = DATEPART( year , CURRENT_TIMESTAMP )" );
                for ( int i=0; i < lineItemList.size(); i++ )
                {
                    ReceivingLineItem receivingLineItem = (ReceivingLineItem) lineItemList.get ( i );
                    if ( receivingLineItem.getUnitCost() == 0 && receivingLineItem.getProcessStepId() > 0 )
                    {
                        pstmt.setInt( 1,  receivingLineItem.getProcessStepId() ); //STANDARD_COST
                        rset = pstmt.executeQuery();
                        if ( rset.next() )
                        {
                            receivingLineItem.setUnitCost( rset.getDouble( "STANDARD_COST" ) );
                        }
                        pstmt.clearParameters();
                        rset.close();
                        rset = null;
                    }
                }
            }
            catch ( Exception e )
            {
                throw new ScalarException ( "Could not load Receivings Line Items Unit Costs", e );
            }
            finally
            {
                if ( rset != null )
                {
                    try { rset.close(); } catch ( SQLException ignore ) {}
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch ( SQLException ignore ) {}
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch ( SQLException ignore ) {}
                }
            }
        }
    }

	/**
	 * @param processStep_Id
	 * @param vendor_Id
	 * @return
	 */
	public static ArrayList loadVendorPartNumbers(String processStep_Id, String vendor_Id) throws ScalarException
	{
        ArrayList vendorPartNumberList 	= new ArrayList();

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        try
        {
            conn  	= DataSource.getConnection();
            pstmt 	= conn.prepareStatement( "SELECT DISTINCT PM.Part_Number FROM PRODUCT P, PRODUCT_MAP_TABLE PM, LOCATION L, Process_Step_Location_Table PSLT WHERE P.ProcessStep_ID = PM.ProcessStep_ID AND P.ProcessStep_ID = PSLT.ProcessStep_ID AND L.Location_ID = PSLT.Location_ID AND PM.Location_ID = L.Location_ID AND P.ProcessStep_Id = ? AND L.Vendor_ID = ? ORDER BY PM.Part_Number" );
            pstmt.setInt( 1, Integer.parseInt(  processStep_Id ) );
            pstmt.setInt( 2, Integer.parseInt(  vendor_Id ) );
            rs 		= pstmt.executeQuery();

            while( rs.next() )
            {
            	vendorPartNumberList.add (rs.getString("Part_Number") );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not Load Vendor Part Number ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in Load Vendor Part Number " );
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in Load Vendor Part Number " );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in Load Vendor Part Number " );
                }
            }
        }
        return vendorPartNumberList;

	}
    public static ArrayList loadVendorSalesAddresses(int vendor_id) throws ScalarException
	{
		return sIPurchaseOrderDAO.loadVendorSalesAddresses( vendor_id );
	}
}
